package leagal_aid.bean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import leagal_aid.util.DBUtil;

public class MessageBean {
	private int id;
	private String name;
	private String telephone;
	private String email;
	private String message;
	private String content;
	private String category;
	private  Date time;

	PreparedStatement statement = null;
	Connection connection = null;
	ResultSet rs = null;
		
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	
	public String getTelephone() {
		return telephone;
	}
	public void setTelephone(String telephone) {
		this.telephone = telephone;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}	
	public String getCategory() {
		return category;
	}
	public void setCategory(String category) {
		this.category = category;
	}
	public String getContent() {
		return content;
	}
	public void setContent(String content) {
		this.content = content;
	}
	public String getMessage() {
		return message;
	}
	public void setMessage(String message) {
		this.message = message;
	}
	public Date getTime() {
		return time;
	}
	public void setTime(Date time) {
		this.time = time;
	}
	public boolean doAdd(String name,String telephone,String email,String content,String category,String time) {
		boolean add = false;
		DBUtil dbUtil = new DBUtil();
		connection = dbUtil.geConnection();
		//String sql = "select password from messageBean where username = " + username + "'";
		String sql = "insert into message (name,telephone,email,content,category,time) values(?,?,?,?,?,?)";
		
	  try {		
		  PreparedStatement statement = connection.prepareStatement(sql);
			statement.setString(1, name);
			statement.setString(2, telephone);
			statement.setString(3, email);
			statement.setString(4,content);
			statement.setString(5,category);
			statement.setString(6,time);
		    int i = statement.executeUpdate();
			if (i>0) {
				add = true;
			}
				
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			dbUtil.close();
		}
		return add;
		
	}
	public List<MessageBean> findAll() {
		List<MessageBean> list = new ArrayList<MessageBean>();
		DBUtil dbUtil = new DBUtil();
		connection = dbUtil.geConnection();
		//String sql = "select password from messageBean where username = " + username + "'";
		String sql = "select * from message";
		
		try {
			statement = connection.prepareStatement(sql);
			rs = statement.executeQuery();
			while(rs.next()) {
				MessageBean messageBean = new MessageBean();
				messageBean.setId(rs.getInt("id"));
				messageBean.setName(rs.getString("name"));
				messageBean.setEmail(rs.getString("email"));
				messageBean.setTelephone(rs.getString("telephone"));
				messageBean.setContent(rs.getString("content"));
				messageBean.setTime(rs.getDate("time"));
				list.add(messageBean);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			} /*
				 * if (statement != null) { try { statement.close(); } catch (SQLException e) {
				 * e.printStackTrace(); } }
				 */
			if(connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			dbUtil.close();
		}
		return list;
		
	}

	public boolean doDel(int id) {
		boolean del = false;
		DBUtil dbUtil = new DBUtil();
		connection = dbUtil.geConnection();
		//String sql = "select password from admin where username = " + username + "'";
		String sql = "delete from message where id =?";
		try {
			statement = connection.prepareStatement(sql);
			statement.setInt(1, id);
			int i = statement.executeUpdate();
			if (i>0) {
				del = true;
			}
				
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			dbUtil.close();
		}
		return del;
		
	}
	public MessageBean findAllById(int id) {
		System.out.println("run findAllById : " + id);
		DBUtil dbUtil = new DBUtil();
		connection = dbUtil.geConnection();
		MessageBean messageBean = new MessageBean();
		try {
			String sql = "select * from message where id = ?";
			statement = connection.prepareStatement(sql);
			statement.setInt(1, id);
			rs = statement.executeQuery();
			
			if(rs.next()) {
				messageBean.setId(rs.getInt("id"));
				messageBean.setName(rs.getString("name"));
				messageBean.setEmail(rs.getString("email"));
				messageBean.setTelephone(rs.getString("telephone"));
				messageBean.setContent(rs.getString("content"));
				messageBean.setTime(rs.getDate("time"));
				System.out.println(messageBean.getName());
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			dbUtil.close();
		}
		System.out.println( "end findAllById :");
		return messageBean;
		
	}
	//分页查询
	 public int findbackCountByLb(String lbString) {
	 	DBUtil dbUtil = new DBUtil();
	 	connection = dbUtil.geConnection();
	 	int count = 0;
	 	String sql = "select count(*) totalCount from message where category =? ";
	 	try {
	 		statement = connection.prepareStatement(sql);
	 		statement.setString(1, lbString);
	 		rs = statement.executeQuery();
	 		if (rs.next()) {
	 			count=rs.getInt("totalCount");
	 		}
	 		
	 	} catch (SQLException e) {
	 		e.printStackTrace();
	 	}finally {
	 		if (rs != null) {
	 			try {
	 				rs.close();
	 			} catch (SQLException e) {
	 				e.printStackTrace();
	 			}
	 		}
	 		if (statement != null) {
	 			try {
	 				statement.close();
	 			} catch (SQLException e) {
	 				e.printStackTrace();
	 			}
	 		}
	 		if(connection != null) {
	 			try {
	 				connection.close();
	 			} catch (SQLException e) {
	 				e.printStackTrace();
	 			}
	 		}
	 		dbUtil.close();
	 	}
	 	return count;
	 }
	 //后端分页
	 public List<MessageBean> findbackByLbForFY(String lbString,Page page) {
	 	List<MessageBean> list = new ArrayList<MessageBean>();
	 	DBUtil dbUtil = new DBUtil();
	 	connection = dbUtil.geConnection();
	 	String sql = "select * from message where category =? order by time desc limit ?,? ";
	 	try {
	 		statement = connection.prepareStatement(sql);
	 		statement.setString(1, lbString);
	 		statement.setInt(2, page.getBeginIndex());
	 		statement.setInt(3, page.getEveryPage());
	 		rs = statement.executeQuery();
	 		while(rs.next()) {
	 			MessageBean messageBean = new MessageBean();
	 			   messageBean.setId(rs.getInt("id"));
	 			   messageBean.setName(rs.getString("name"));
	 			   messageBean.setEmail(rs.getString("email"));
	 			   messageBean.setTelephone(rs.getString("telephone"));
	 			   messageBean.setContent(rs.getString("content"));
	 			   messageBean.setCategory(rs.getString("category"));
	 			   messageBean.setTime(rs.getDate("time"));
	 			System.out.println(messageBean.getName());
	 			list.add(messageBean);
	 		}
	 		
	 	} catch (SQLException e) {
	 		e.printStackTrace();
	 	}finally {
	 		if (rs != null) {
	 			try {
	 				rs.close();
	 			} catch (SQLException e) {
	 				e.printStackTrace();
	 			}
	 		}
	 		if (statement != null) {
	 			try {
	 				statement.close();
	 			} catch (SQLException e) {
	 				e.printStackTrace();
	 			}
	 		}
	 		if(connection != null) {
	 			try {
	 				connection.close();
	 			} catch (SQLException e) {
	 				e.printStackTrace();
	 			}
	 		}
	 		dbUtil.close();
	 	}
	 	return list;
	 	
	 }
}
